'''
class类为数据库的相关内容
返回的数据不同，出现的问题不同
1 正常
0 连接数据库失败
2 数据库连接成功但是插入失败
3 数据库有该人信息
4 数据库没有该人的信息
5 一切正常，打卡失败
'''
import pymysql   # 连接数据库
# 数据库信息
dbhost = 'localhost'
dbuser = 'root'
dbpass = '200814'
dbname = 'face'

class mysql:
    def __init__(self, name = "", banji = " ", id ="",gender= " ", phone = "", face = " "):
        self.id = id
        self.name = name
        self.banji = banji
        self.face = face
        self.phone = phone
        self.gender = gender

    def connect(self):  # 连接数据库
        try:
            db = pymysql.connect(
                host = dbhost # 连接名称，默认127.0.0.1 
                ,user = dbuser # 用户名
                ,passwd = dbpass # 密码
                ,port = 3306 # 端口，默认为3306
                ,db = dbname # 数据库名称
                ,charset = 'utf8' # 字符编码
                )
        except pymysql.Error as e:
            return 0

        return db

    def insert_basics(self):  # 向mysql中插入人脸信息数据
        db = self.connect()
        if db ==0:
            return 0
        else:
            cur = db.cursor() # 生成游标对象

        # 查询数据库中是否有该人的人脸信息
        sql="SELECT * FROM face_image WHERE id = '" + self.id + "';" # SQL语句
        cur.execute(sql) # 执行SQL语句
        data = cur.fetchall() # 返回你要的数据

        # 如果没有该人的信息
        if not data:

            sqlQuery=" INSERT INTO face_image(id, name,class,gender,phone, face) VALUE ('" + self.id + "', '" + self.name + "', '" +self.banji+ "', '" + self.gender + "', '"+self.phone + "', '" + self.face + "'); "

            try:
                cur.execute(sqlQuery)
                db.commit()
                # 如果插入成功返回1
                return 1
            except pymysql.Error as e:
                # 如果插入失败，返回0
                # print("数据插入失败：" + e )
                db.rollback()
                return 2

        # 如果有该人的人脸信息，则更新该人脸信息
        elif  data:
            # print("有该人脸信息")
            return 3

        cur.close() # 关闭游标
        db.close() # 关闭连接

    def insert_face(self):  # 向mysql中插入人脸信息数据
        db = self.connect()
        if db ==0:
            return 0
        else:
            cur = db.cursor() # 生成游标对象

        # 查询数据库中是否有该人的人脸信息
        sql="SELECT * FROM face_image WHERE id = '" + self.id + "';" # SQL语句
        cur.execute(sql) # 执行SQL语句
        data = cur.fetchall() # 返回你要的数据

        # 如果没有该人的信息
        if not data:
            return 4
            
        # 如果有该人的信息，则更新该人脸信息
        elif  data:
            sqlQuery = "UPDATE face_image SET face = '" + self.face + "' WHERE id = '" + self.id + "';"
            try:
                cur.execute(sqlQuery)
                db.commit()
                # 如果插入成功返回1
                return 1
            except pymysql.Error as e:
                # 如果插入失败，返回0
                # print("数据插入失败：" + e )
                db.rollback()
                return 2
        cur.close() # 关闭游标
        db.close() # 关闭连接

    # 查找基础信息
    def lookup(self):
        db = self.connect()
        if db ==0:
            return 0
        else:
            cur = db.cursor() # 生成游标对象
            
        # 查询数据库中是否有该人的人脸信息
        sql="SELECT * FROM face_image WHERE id = '" + self.id + "';" # SQL语句
        cur.execute(sql) # 执行SQL语句
        data = cur.fetchall() # 返回你要的数据
        cur.close() # 关闭游标
        db.close() # 关闭连接
        if not data:
            return 4
        elif data:
            self.id = data[0][0]
            self.name = data[0][1]
            self.banji = data[0][2]
            self.gender = data[0][3]
            self.phone = data[0][4]
            return 1

    def updata(self, leixing = "", xinxi=""):
        db = self.connect()
        if db ==0:
            return 0
        else:
            cur = db.cursor() # 生成游标对象

        # 查询数据库中是否有该人的人脸信息
        sql="SELECT * FROM face_image WHERE id = '" + self.id + "';" # SQL语句
        cur.execute(sql) # 执行SQL语句
        data = cur.fetchall() # 返回你要的数据

        # 如果没有该人的信息
        if not data:
            return 3
        elif data:
            sqlQuery = "UPDATE face_image SET " + leixing + " = '" + xinxi + "' WHERE id = '" + self.id + "';"
            try:
                cur.execute(sqlQuery)
                db.commit()
                return 1
            except pymysql.Error as e:
                print("数据更新失败：" + str(e) )
                db.rollback()
                return 2


    def delete(self):
        db = self.connect()
        if db ==0:
            return 0
        else:
            cur = db.cursor() # 生成游标对象

        # 查询数据库中是否有该人的人脸信息
        sql="SELECT * FROM face_image WHERE id = '" + self.id + "'"   # SQL语句
        cur.execute(sql) # 执行SQL语句
        data = cur.fetchall() # 返回你要的数据

        # 如果没有该人的人脸信息
        if not data:
            return 4

        # 如果有该人的人脸信息，则进行
        elif  data:
            sqlQuery= " DELETE FROM face_image WHERE id = '" + self.id + "';"
            try:
                cur.execute(sqlQuery)
                db.commit()
                return 1
            except pymysql.Error as e:
                # print("数据删除失败：" + e )
                db.rollback()
                return 2


        cur.close() # 关闭游标
        db.close() # 关闭连接

    def select_kaoqin(self, didian = " "):  # 向mysql中查找人脸信息数据
        #  班级内打卡人员名字和未打卡人员名字的统计
        self.banji_daka = ""
        self.banji_weidaka = ""
        # 班级内应到人数和实到人数的统计
        self.yingdao = 0
        self.shidao = 0
        
        db = self.connect()
        if db ==0:
            return 0
        else:
            cur = db.cursor() # 生成游标对象
            
        # 查询数据库中信息
        sql="SELECT * FROM face_image WHERE class = '" + self.banji + "'AND daka = '1' AND daka_didian = '" + didian + "';" # SQL语句
        cur.execute(sql) # 执行SQL语句
        daka_data = cur.fetchall() # 返回你要的数据
         # 查询数据库中信息
        sql="SELECT * FROM face_image WHERE class = '" + self.banji + "'AND ( daka != '1' OR daka_didian !='" + didian + "');" # SQL语句
        cur.execute(sql) # 执行SQL语句
        weidaka_data = cur.fetchall() # 返回你要的数据
        
        cur.close() # 关闭游标
        db.close() # 关闭连接
        
        if (not daka_data) and (not weidaka_data):
            return 3
        
        else:
            if daka_data :
                for i in daka_data:
                    self.banji_daka += i[1]  + "；" 
                    self.shidao += 1
                    self.yingdao +=1 
        
            if weidaka_data:
                for j in weidaka_data:
                    self.banji_weidaka += j[1]  + "；"
                    self.yingdao +=1

            return 1
            

    def select(self):  # 向mysql中查找人脸信息数据
        db = self.connect()
        if db ==0:
            return 0
        else:
            cur = db.cursor() # 生成游标对象
            
        # 查询数据库中是否有该人的人脸信息
        if self.id:
            sql="SELECT * FROM face_image WHERE id = '" + self.id + "';" # SQL语句
        elif self.name:
            sql="SELECT * FROM face_image WHERE name = '" + self.name + "';" # SQL语句
        
        cur.execute(sql) # 执行SQL语句
        data = cur.fetchall() # 返回你要的数据
        cur.close() # 关闭游标
        db.close() # 关闭连接
        if not data:
            return 3
        elif data:
            self.face = data[0][7]
            return 1
        
    def daka(self,didian = ""):
        db = self.connect()
        if db ==0:
            return 0
        else:
            cur = db.cursor() # 生成游标对象
        
        # 查询数据库中是否有该人的人脸信息
        if self.id:
            sqlQuery="UPDATE face_image SET daka = '1', daka_didian = '"+ didian+"' WHERE id = '" + self.id + "';" # SQL语句
        elif self.name:
            sqlQuery="UPDATE face_image SET daka = '1', daka_didian = '"+ didian+"' WHERE name = '" + self.name + "';" # SQL语句
        try:
            cur.execute(sqlQuery)
            db.commit()
            return 1

        except pymysql.Error as e:

            db.rollback()
            return 5
    
    def buqian(self,didian = ""):
        db = self.connect()
        if db ==0:
            return 0
        else:
            cur = db.cursor() # 生成游标对象
            
        # 查询数据库中是否有该人的人脸信息
        if self.id:
            sql="SELECT * FROM face_image WHERE id = '" + self.id + "';" # SQL语句
        elif self.name:
            sql="SELECT * FROM face_image WHERE name = '" + self.name + "';" # SQL语句
        
        cur.execute(sql) # 执行SQL语句
        data = cur.fetchall() # 返回你要的数据
        if not data:
            return 3
        elif data:
            if self.id:
                sqlQuery="UPDATE face_image SET daka = '1',daka_didian = '" + didian + "' WHERE id = '" + self.id + "';" # SQL语句
            elif self.name:
                sqlQuery="UPDATE face_image SET daka = '1' ,daka_didian = '" + didian + "' WHERE name = '" + self.name + "';" # SQL语句
            try:
                cur.execute(sqlQuery)
                db.commit()
                return 1
            except pymysql.Error as e:
                print(e)
                db.rollback()
                return 5
            
        cur.close() # 关闭游标
        db.close() # 关闭连接



    def qingjia(self):
        db = self.connect()
        if db ==0:
            return 0
        else:
            cur = db.cursor() # 生成游标对象
            
        # 查询数据库中是否有该人的人脸信息
        if self.id:
            sql="SELECT * FROM face_image WHERE id = '" + self.id + "';" # SQL语句
        elif self.name:
            sql="SELECT * FROM face_image WHERE name = '" + self.name + "';" # SQL语句
        
        cur.execute(sql) # 执行SQL语句
        data = cur.fetchall() # 返回你要的数据
        if not data:
            return 3
        elif data:
            if self.id:
                sqlQuery="UPDATE face_image SET daka = '请假',daka_didian = '0' WHERE id = '" + self.id + "';" # SQL语句
            elif self.name:
                sqlQuery="UPDATE face_image SET daka = '请假' ,daka_didian = '0' WHERE name = '" + self.name + "';" # SQL语句
            try:
                cur.execute(sqlQuery)
                db.commit()
                return 1
            except pymysql.Error as e:
                print(e)
                db.rollback()
                return 5
            
        cur.close() # 关闭游标
        db.close() # 关闭连接
        



